********************************************************************************
** 	TITLE:		DE21_results                                                  ** 	
**	AUTHOR:	    Philippe Mongrain                                             **
**	DATA:       btw21_kerg                                                    **
**	VERSION:	Stata 16					                                  **
**	DATE:		October 2022  				                                  **
********************************************************************************

* Version control

version 16.0

* 1. Download the "btw21_kerg.csv" file from this link: https://www.bundeswahlleiter.de/en/dam/jcr/fc2afe29-c086-43eb-bc34-a48356dee154/btw21_kerg.csv
* 2. Save the "btw21_kerg.csv" file to the Excel format (i.e., "btw21_kerg.xlsx"")
* 3. Verify that the name of the Excel sheet is "btw21_kerg"

* Import the dataset

import excel "btw21_kerg.xlsx", sheet("btw21_kerg") cellrange(A6:HC337) clear

* Rename variables

rename A districtnumber21 
rename B districtname21 
rename C state21 
rename D v21_1_Wahlberechtigte_1_End
rename E v17_1_Wahlberechtigte_1_Vor
rename F v21_2_Wahlberechtigte_2_End
rename G v17_2_Wahlberechtigte_2_Vor
rename H v21_1_Wähler_1_End
rename I v17_1_Wähler_1_Vor
rename J v21_2_Wähler_2_End
rename K v17_2_Wähler_2_Vor
rename L v21_1_Ungültige_1_End
rename M v17_1_Ungültige_1_Vor
rename N v21_2_Ungültige_2_End
rename O v17_2_Ungültige_2_Vor
rename P v21_1_Gültige_1_End
rename Q v17_1_Gültige_1_Vor
rename R v21_2_Gültige_2_End
rename S v17_2_Gültige_2_Vor
rename T v21_1_CDU_1_End
rename U v17_1_CDU_1_Vor
rename V v21_2_CDU_2_End
rename W v17_2_CDU_2_Vor
rename X v21_1_SPD_1_End
rename Y v17_1_SPD_1_Vor
rename Z v21_2_SPD_2_End
rename AA v17_2_SPD_2_Vor
rename AB v21_1_AfD_1_End
rename AC v17_1_AfD_1_Vor
rename AD v21_2_AfD_2_End
rename AE v17_2_AfD_2_Vor
rename AF v21_1_FDP_1_End
rename AG v17_1_FDP_1_Vor
rename AH v21_2_FDP_2_End
rename AI v17_2_FDP_2_Vor
rename AJ v21_1_DIELINKE_1_End
rename AK v17_1_DIELINKE_1_Vor
rename AL v21_2_DIELINKE_2_End
rename AM v17_2_DIELINKE_2_Vor
rename AN v21_1_GRÜNE_1_End
rename AO v17_1_GRÜNE_1_Vor
rename AP v21_2_GRÜNE_2_End
rename AQ v17_2_GRÜNE_2_Vor
rename AR v21_1_CSU_1_End
rename AS v17_1_CSU_1_Vor
rename AT v21_2_CSU_2_End
rename AU v17_2_CSU_2_Vor
rename AV v21_1_FREIEWÄHLER_1_End
rename AW v17_1_FREIEWÄHLER_1_Vor
rename AX v21_2_FREIEWÄHLER_2_End
rename AY v17_2_FREIEWÄHLER_2_Vor
rename AZ v17_2_DiePARTEI_1_End
rename BA v17_1_DiePARTEI_1_Vor
rename BB v21_2_DiePARTEI_2_End
rename BC v17_2_DiePARTEI_2_Vor
rename BD v21_1_Tierschutzpartei_1_End
rename BE v17_1_Tierschutzpartei_1_Vor
rename BF v21_2_Tierschutzpartei_2_End
rename BG v17_2_Tierschutzpartei_2_Vor
rename BH v21_1_NPD_1_End
rename BI v17_1_NPD_1_Vor
rename BJ v21_2_NPD_2_End
rename BK v17_2_NPD_2_Vor
rename BL v21_1_PIRATEN_1_End
rename BM v17_1_PIRATEN_1_Vor
rename BN v21_2_PIRATEN_2_End
rename BO v17_2_PIRATEN_2_Vor
rename BP v21_1_ÖDP_1_End
rename BQ v17_1_ÖDP_1_Vor
rename BR v21_2_ÖDP_2_End
rename BS v17_2_ÖDP_2_Vor
rename BT v21_1_VPartei3_1_End
rename BU v17_1_VPartei3_1_Vor
rename BV v21_2_VPartei3_2_End
rename BW v17_2_VPartei3_2_Vor
rename BX v21_1_DiB_1_End
rename BY v17_1_DiB_1_Vor
rename BZ v21_2_DiB_2_End
rename CA v17_2_DiB_2_Vor
rename CB v21_1_BP_1_End
rename CC v17_1_BP_1_Vor
rename CD v21_2_BP_2_End
rename CE v17_2_BP_2_Vor
rename CF v21_1_Tierschutzallianz_1_End
rename CG v17_1_Tierschutzallianz_1_Vor
rename CH v21_2_Tierschutzallianz_2_End
rename CI v17_2_Tierschutzallianz_2_Vor
rename CJ v17_1_MLPD_1_Vor
rename CK v21_2_MLPD_2_End
rename CL v17_2_MLPD_2_Vor
rename CM v21_1_MLPD_1_End
rename CN v17_1_Gesund_1_Vor
rename CO v21_2_Gesund_2_End
rename CP v17_2_Gesund_2_Vor
rename CQ v21_1_Gesund_1_End
rename CR v21_1_MW_1_End
rename CS v17_1_MW_1_Vor
rename CT v21_2_MW_2_End
rename CU v17_2_MW_2_Vor
rename CV v21_1_DKP_1_End
rename CW v17_1_DKP_1_Vor
rename CX v21_2_DKP_2_End
rename CY v17_2_DKP_2_Vor
rename CZ v21_1_DieGrauen_1_End
rename DA v17_1_DieGrauen_1_Vor
rename DB v21_2_DieGrauen_2_End
rename DC v17_2_DieGrauen_2_Vor
rename DD v21_1_BüSo_1_End
rename DE v17_1_BüSo_1_Vor
rename DF v21_2_BüSo_2_End
rename DG v17_2_BüSo_2_Vor
rename DH v21_1_DieHumanisten_1_End
rename DI v17_1_DieHumanisten_1_Vor
rename DJ v21_2_DieHumanisten_2_End
rename DK v17_2_DieHumanisten_2_Vor
rename DL v21_1_Gartenpartei_1_End
rename DM v17_1_Gartenpartei_1_Vor
rename DN v21_2_Gartenpartei_2_End
rename DO v17_2_Gartenpartei_2_Vor
rename DP v21_1_DieUrbane_1_End
rename DQ v17_1_DieUrbane_1_Vor
rename DR v21_2_DieUrbane_2_End
rename DS v17_2_DieUrbane_2_Vor
rename DT v21_1_SGP_1_End
rename DU v17_1_SGP_1_Vor
rename DV v21_2_SGP_2_End
rename DW v17_2_SGP_2_Vor
rename DX v21_1_dieBasis_1_End
rename DY v17_1_dieBasis_1_Vor
rename DZ v21_2_dieBasis_2_End
rename EA v17_2_dieBasis_2_Vor
rename EB v21_1_BündnisC_1_End
rename EC v17_1_BündnisC_1_Vor
rename ED v21_2_BündnisC_2_End
rename EE v17_2_BündnisC_2_Vor
rename EF v21_1_BFW_1_End
rename EG v17_1_BFW_1_Vor
rename EH v21_2_BFW_2_End
rename EI v17_2_BFW_2_Vor
rename EJ v21_1_DERDRITTEWEG_1_End
rename EK v17_1_DERDRITTEWEG_1_Vor
rename EL v21_2_DERDRITTEWEG_2_End
rename EM v17_2_DERDRITTEWEG_2_Vor
rename EN v21_1_BÜNDNIS21_1_End
rename EO v17_1_BÜNDNIS21_1_Vor
rename EP v21_2_BÜNDNIS21_2_End
rename EQ v17_2_BÜNDNIS21_2_Vor
rename ER v21_1_EPL_1_End
rename ES v17_1_EPL_1_Vor
rename ET v21_2_EPL_2_End
rename EU v17_2_EPL_2_Vor
rename EV v21_1_LKR_1_End
rename EW v17_1_LKR_1_Vor
rename EX v21_2_LKR_2_End
rename EY v17_2_LKR_2_Vor
rename EZ v21_1_PdF_1_End
rename FA v17_1_PdF_1_Vor
rename FB v21_2_PdF_2_End
rename FC v17_2_PdF_2_Vor
rename FD v21_1_LfK_1_End
rename FE v17_1_LfK_1_Vor
rename FF v21_2_LfK_2_End
rename FG v17_2_LfK_2_Vor
rename FH v21_1_SSW_1_End
rename FI v17_1_SSW_1_Vor
rename FJ v21_2_SSW_2_End
rename FK v17_2_SSW_2_Vor
rename FL v21_1_Todenhöfer_1_End
rename FM v17_1_Todenhöfer_1_Vor
rename FN v21_2_Todenhöfer_2_End
rename FO v17_2_Todenhöfer_2_Vor
rename FP v21_1_UNABHÄNGIGE_1_End
rename FQ v17_1_UNABHÄNGIGE_1_Vor
rename FR v21_2_UNABHÄNGIGE_2_End
rename FS v17_2_UNABHÄNGIGE_2_Vor
rename FT v21_1_VoltDeutschland_1_End
rename FU v17_1_VoltDeutschland_1_Vor
rename FV v21_2_VoltDeutschland_2_End
rename FW v17_2_VoltDeutschland_2_Vor
rename FX v21_1_Abjetzt_1_End
rename FY v17_1_Abjetzt_1_Vor
rename FZ v21_2_Abjetzt_2_End
rename GA v17_2_Abjetzt_2_Vor
rename GB v21_1_B_1_End
rename GC v17_1_B_1_Vor
rename GD v21_2_B_2_End
rename GE v17_2_B_2_Vor
rename GF v21_1_DIESONSTIGENX_1_End
rename GG v17_1_DIESONSTIGENX_1_Vor
rename GH v21_2_DIESONSTIGENX_2_End
rename GI v17_2_DIESONSTIGENX_2_Vor
rename GJ v21_1_FAMILIE_1_End
rename GK v17_1_FAMILIE_1_Vor
rename GL v21_2_FAMILIE_2_End
rename GM v17_2_FAMILIE_2_Vor
rename GN v21_1_GrauePanther_1_End
rename GO v17_1_GrauePanther_1_Vor
rename GP v21_2_GrauePanther_2_End
rename GQ v17_2_GrauePanther_2_Vor
rename GR v21_1_KlimalisteBW_1_End
rename GS v17_1_KlimalisteBW_1_Vor
rename GT v21_2_KlimalisteBW_2_End
rename GU v17_2_KlimalisteBW_2_Vor
rename GV v21_1_THP_1_End
rename GW v17_1_THP_1_Vor
rename GX v21_2_THP_2_End
rename GY v17_2_THP_2_Vor
rename GZ v21_1_Übrige_1_End
rename HA v17_1_Übrige_1_Vor
rename HB v21_2_Übrige_2_End
rename HC v17_2_Übrige_2_Vor

* Reshape the dataset

drop if districtnumber21 == .
drop if districtname21 == "Bundesgebiet"
drop districtname21
drop if state21 == 99
drop state21

reshape long v21_1_ v17_1_ v21_2_ v17_2_, i(districtnumber21) j(var) string

* Drop second vote and 2021 results

drop if strpos(var, "_2_") > 0
drop if strpos(var, "_End") > 0
drop v21_1_ v21_2_ v17_2_

* Generate dichotmous variable for each party 

gen cdu17 = strpos(var, "CDU") > 0
gen spd17 = strpos(var, "SPD") > 0
gen dielinke17 = strpos(var, "DIELINKE") > 0
gen grune17 = strpos(var, "GRÜNE") > 0
gen csu17 = strpos(var, "CSU") > 0
gen fdp17 = strpos(var, "FDP") > 0
gen afd17 = strpos(var, "AfD") > 0
gen piraten17 = strpos(var, "PIRATEN") > 0
gen ndp17 = strpos(var, "NPD") > 0
gen freiewahler17 = strpos(var, "FREIEWÄHLER") > 0
gen tier17 = strpos(var, "Tierschutzpartei") > 0
gen odp17 = strpos(var, "ÖDP") > 0
gen diepartei17 = strpos(var, "DiePARTEI") > 0
gen bp17 = strpos(var, "BP") > 0
gen abjetzt17 = strpos(var, "Abjetzt") > 0
gen mlpd17 = strpos(var, "MLPD") > 0
gen buso17 = strpos(var, "BüSo") > 0
gen sgp17 = strpos(var, "SGP") > 0
gen dierechte17 = strpos(var, "DIERECHTE") > 0
gen tierall17 = strpos(var, "Tierschutzallianz") > 0
gen bge17 = strpos(var, "BGE") > 0
gen dib17 = strpos(var, "DiB") > 0
gen dkp17 = strpos(var, "DKP") > 0
gen dm17 = strpos(var, "DM") > 0
gen diegrauen17 = strpos(var, "DieGrauen") > 0
gen dieurbane17 = strpos(var, "DieUrbane") > 0
gen garten17 = strpos(var, "Gartenpartei") > 0
gen mw17 = strpos(var, "MW") > 0
gen human17 = strpos(var, "DieHumanisten") > 0
gen gesund17 = strpos(var, "Gesund") > 0
gen vpartei17 = strpos(var, "VPartei3") > 0
gen bundnisc17 = strpos(var, "BündnisC") > 0
gen familie17 = strpos(var, "FAMILIE") > 0
gen dieBasis17 = strpos(var, "dieBasis") > 0
gen bfw17 = strpos(var, "BFW") > 0
gen dritte17 = strpos(var, "DERDRITTEWEG") > 0
gen bundnis17 = strpos(var, "BÜNDNIS21") > 0
gen epl17 = strpos(var, "EPL") > 0
gen lkr17 = strpos(var, "LKR") > 0
gen pdf17 = strpos(var, "PdF") > 0
gen lfk17 = strpos(var, "LfK") > 0
gen ssw17 = strpos(var, "SSW") > 0
gen todenhofer17 = strpos(var, "Todenhöfer") > 0
gen b17 = strpos(var, "B") > 0
gen graue17 = strpos(var, "GrauePanther") > 0
gen sonstigen17 = strpos(var, "DIESONSTIGENX") > 0
gen kbw17 = strpos(var, "KlimalisteBW") > 0
gen thp17 = strpos(var, "THP") > 0
gen unab17 = strpos(var, "UNABHÄNGIGE") > 0
gen other17 = strpos(var, "Übrige") > 0

* Generate district names

gen districtname21 = ""

replace districtname21 = "Flensburg – Schleswig" if districtnumber21 == 1
replace districtname21 = "Nordfriesland – Dithmarschen Nord" if districtnumber21 == 2
replace districtname21 = "Steinburg – Dithmarschen Süd" if districtnumber21 == 3
replace districtname21 = "Rendsburg-Eckernförde" if districtnumber21 == 4
replace districtname21 = "Kiel" if districtnumber21 == 5
replace districtname21 = "Plön – Neumünster" if districtnumber21 == 6
replace districtname21 = "Pinneberg" if districtnumber21 == 7
replace districtname21 = "Segeberg – Stormarn-Mitte" if districtnumber21 == 8
replace districtname21 = "Ostholstein – Stormarn-Nord" if districtnumber21 == 9
replace districtname21 = "Herzogtum Lauenburg – Stormarn-Süd" if districtnumber21 == 10
replace districtname21 = "Lübeck" if districtnumber21 == 11
replace districtname21 = "Schwerin – Ludwigslust-Parchim I – Nordwestmecklenburg I" if districtnumber21 == 12
replace districtname21 = "Ludwigslust-Parchim II – Nordwestmecklenburg II – Landkreis Rostock I" if districtnumber21 == 13
replace districtname21 = "Rostock – Landkreis Rostock II" if districtnumber21 == 14
replace districtname21 = "Vorpommern-Rügen – Vorpommern-Greifswald I" if districtnumber21 == 15
replace districtname21 = "Mecklenburgische Seenplatte I – Vorpommern-Greifswald II" if districtnumber21 == 16
replace districtname21 = "Mecklenburgische Seenplatte II – Landkreis Rostock III" if districtnumber21 == 17
replace districtname21 = "Hamburg-Mitte" if districtnumber21 == 18
replace districtname21 = "Hamburg-Altona" if districtnumber21 == 19
replace districtname21 = "Hamburg-Eimsbüttel" if districtnumber21 == 20
replace districtname21 = "Hamburg-Nord" if districtnumber21 == 21
replace districtname21 = "Hamburg-Wandsbek" if districtnumber21 == 22
replace districtname21 = "Hamburg-Bergedorf – Harburg" if districtnumber21 == 23
replace districtname21 = "Aurich – Emden" if districtnumber21 == 24
replace districtname21 = "Unterems" if districtnumber21 == 25
replace districtname21 = "Friesland – Wilhelmshaven – Wittmund" if districtnumber21 == 26
replace districtname21 = "Oldenburg – Ammerland" if districtnumber21 == 27
replace districtname21 = "Delmenhorst – Wesermarsch – Oldenburg-Land" if districtnumber21 == 28
replace districtname21 = "Cuxhaven – Stade II" if districtnumber21 == 29
replace districtname21 = "Stade I – Rotenburg II" if districtnumber21 == 30
replace districtname21 = "Mittelems" if districtnumber21 == 31
replace districtname21 = "Cloppenburg – Vechta" if districtnumber21 == 32
replace districtname21 = "Diepholz – Nienburg I" if districtnumber21 == 33
replace districtname21 = "Osterholz – Verden" if districtnumber21 == 34
replace districtname21 = "Rotenburg I – Heidekreis" if districtnumber21 == 35
replace districtname21 = "Harburg" if districtnumber21 == 36
replace districtname21 = "Lüchow-Dannenberg – Lüneburg" if districtnumber21 == 37
replace districtname21 = "Osnabrück-Land" if districtnumber21 == 38
replace districtname21 = "Stadt Osnabrück" if districtnumber21 == 39
replace districtname21 = "Nienburg II – Schaumburg" if districtnumber21 == 40
replace districtname21 = "Stadt Hannover I" if districtnumber21 == 41
replace districtname21 = "Stadt Hannover II" if districtnumber21 == 42
replace districtname21 = "Hannover-Land I" if districtnumber21 == 43
replace districtname21 = "Celle – Uelzen" if districtnumber21 == 44
replace districtname21 = "Gifhorn – Peine" if districtnumber21 == 45
replace districtname21 = "Hameln-Pyrmont – Holzminden" if districtnumber21 == 46
replace districtname21 = "Hannover-Land II" if districtnumber21 == 47
replace districtname21 = "Hildesheim" if districtnumber21 == 48
replace districtname21 = "Salzgitter – Wolfenbüttel" if districtnumber21 == 49
replace districtname21 = "Braunschweig" if districtnumber21 == 50
replace districtname21 = "Helmstedt – Wolfsburg" if districtnumber21 == 51
replace districtname21 = "Goslar – Northeim – Osterode" if districtnumber21 == 52
replace districtname21 = "Göttingen" if districtnumber21 == 53
replace districtname21 = "Bremen I" if districtnumber21 == 54
replace districtname21 = "Bremen II – Bremerhaven" if districtnumber21 == 55
replace districtname21 = "Prignitz – Ostprignitz-Ruppin – Havelland I" if districtnumber21 == 56
replace districtname21 = "Uckermark – Barnim I" if districtnumber21 == 57
replace districtname21 = "Oberhavel – Havelland II" if districtnumber21 == 58
replace districtname21 = "Märkisch-Oderland – Barnim II" if districtnumber21 == 59
replace districtname21 = "Brandenburg an der Havel – Potsdam-Mittelmark I – Havelland III – Teltow-Fläming I" if districtnumber21 == 60
replace districtname21 = "Potsdam – Potsdam-Mittelmark II – Teltow-Fläming II" if districtnumber21 == 61
replace districtname21 = "Dahme-Spreewald – Teltow-Fläming III – Oberspreewald-Lausitz I" if districtnumber21 == 62
replace districtname21 = "Frankfurt (Oder) – Oder-Spree" if districtnumber21 == 63
replace districtname21 = "Cottbus – Spree-Neiße" if districtnumber21 == 64
replace districtname21 = "Elbe-Elster – Oberspreewald-Lausitz II" if districtnumber21 == 65
replace districtname21 = "Altmark" if districtnumber21 == 66
replace districtname21 = "Börde – Jerichower Land" if districtnumber21 == 67
replace districtname21 = "Harz" if districtnumber21 == 68
replace districtname21 = "Magdeburg" if districtnumber21 == 69
replace districtname21 = "Dessau – Wittenberg" if districtnumber21 == 70
replace districtname21 = "Anhalt" if districtnumber21 == 71
replace districtname21 = "Halle" if districtnumber21 == 72
replace districtname21 = "Burgenland – Saalekreis" if districtnumber21 == 73
replace districtname21 = "Mansfeld" if districtnumber21 == 74
replace districtname21 = "Berlin-Mitte" if districtnumber21 == 75
replace districtname21 = "Berlin-Pankow" if districtnumber21 == 76
replace districtname21 = "Berlin-Reinickendorf" if districtnumber21 == 77
replace districtname21 = "Berlin-Spandau – Charlottenburg Nord" if districtnumber21 == 78
replace districtname21 = "Berlin-Steglitz-Zehlendorf" if districtnumber21 == 79
replace districtname21 = "Berlin-Charlottenburg-Wilmersdorf" if districtnumber21 == 80
replace districtname21 = "Berlin-Tempelhof-Schöneberg" if districtnumber21 == 81
replace districtname21 = "Berlin-Neukölln" if districtnumber21 == 82
replace districtname21 = "Berlin-Friedrichshain-Kreuzberg – Prenzlauer Berg Ost" if districtnumber21 == 83
replace districtname21 = "Berlin-Treptow-Köpenick" if districtnumber21 == 84
replace districtname21 = "Berlin-Marzahn-Hellersdorf" if districtnumber21 == 85
replace districtname21 = "Berlin-Lichtenberg" if districtnumber21 == 86
replace districtname21 = "Aachen I" if districtnumber21 == 87
replace districtname21 = "Aachen II" if districtnumber21 == 88
replace districtname21 = "Heinsberg" if districtnumber21 == 89
replace districtname21 = "Düren" if districtnumber21 == 90
replace districtname21 = "Rhein-Erft-Kreis I" if districtnumber21 == 91
replace districtname21 = "Euskirchen – Rhein-Erft-Kreis II" if districtnumber21 == 92
replace districtname21 = "Köln I" if districtnumber21 == 93
replace districtname21 = "Köln II" if districtnumber21 == 94
replace districtname21 = "Köln III" if districtnumber21 == 95
replace districtname21 = "Bonn" if districtnumber21 == 96
replace districtname21 = "Rhein-Sieg-Kreis I" if districtnumber21 == 97
replace districtname21 = "Rhein-Sieg-Kreis II" if districtnumber21 == 98
replace districtname21 = "Oberbergischer Kreis" if districtnumber21 == 99
replace districtname21 = "Rheinisch-Bergischer Kreis" if districtnumber21 == 100
replace districtname21 = "Leverkusen – Köln IV" if districtnumber21 == 101
replace districtname21 = "Wuppertal I" if districtnumber21 == 102
replace districtname21 = "Solingen – Remscheid – Wuppertal II" if districtnumber21 == 103
replace districtname21 = "Mettmann I" if districtnumber21 == 104
replace districtname21 = "Mettmann II" if districtnumber21 == 105
replace districtname21 = "Düsseldorf I" if districtnumber21 == 106
replace districtname21 = "Düsseldorf II" if districtnumber21 == 107
replace districtname21 = "Neuss I" if districtnumber21 == 108
replace districtname21 = "Mönchengladbach" if districtnumber21 == 109
replace districtname21 = "Krefeld I – Neuss II" if districtnumber21 == 110
replace districtname21 = "Viersen" if districtnumber21 == 111
replace districtname21 = "Kleve" if districtnumber21 == 112
replace districtname21 = "Wesel I" if districtnumber21 == 113
replace districtname21 = "Krefeld II – Wesel II" if districtnumber21 == 114
replace districtname21 = "Duisburg I" if districtnumber21 == 115
replace districtname21 = "Duisburg II" if districtnumber21 == 116
replace districtname21 = "Oberhausen – Wesel III" if districtnumber21 == 117
replace districtname21 = "Mülheim – Essen I" if districtnumber21 == 118
replace districtname21 = "Essen II" if districtnumber21 == 119
replace districtname21 = "Essen III" if districtnumber21 == 120
replace districtname21 = "Recklinghausen I" if districtnumber21 == 121
replace districtname21 = "Recklinghausen II" if districtnumber21 == 122
replace districtname21 = "Gelsenkirchen" if districtnumber21 == 123
replace districtname21 = "Steinfurt I – Borken I" if districtnumber21 == 124
replace districtname21 = "Bottrop – Recklinghausen III" if districtnumber21 == 125
replace districtname21 = "Borken II" if districtnumber21 == 126
replace districtname21 = "Coesfeld – Steinfurt II" if districtnumber21 == 127
replace districtname21 = "Steinfurt III" if districtnumber21 == 128
replace districtname21 = "Münster" if districtnumber21 == 129
replace districtname21 = "Warendorf" if districtnumber21 == 130
replace districtname21 = "Gütersloh I" if districtnumber21 == 131
replace districtname21 = "Bielefeld – Gütersloh II" if districtnumber21 == 132
replace districtname21 = "Herford – Minden-Lübbecke II" if districtnumber21 == 133
replace districtname21 = "Minden-Lübbecke I" if districtnumber21 == 134
replace districtname21 = "Lippe I" if districtnumber21 == 135
replace districtname21 = "Höxter – Lippe II" if districtnumber21 == 136
replace districtname21 = "Paderborn – Gütersloh III" if districtnumber21 == 137
replace districtname21 = "Hagen – Ennepe-Ruhr-Kreis I" if districtnumber21 == 138
replace districtname21 = "Ennepe-Ruhr-Kreis II" if districtnumber21 == 139
replace districtname21 = "Bochum I" if districtnumber21 == 140
replace districtname21 = "Herne – Bochum II" if districtnumber21 == 141
replace districtname21 = "Dortmund I" if districtnumber21 == 142
replace districtname21 = "Dortmund II" if districtnumber21 == 143
replace districtname21 = "Unna I" if districtnumber21 == 144
replace districtname21 = "Hamm – Unna II" if districtnumber21 == 145
replace districtname21 = "Soest" if districtnumber21 == 146
replace districtname21 = "Hochsauerlandkreis" if districtnumber21 == 147
replace districtname21 = "Siegen-Wittgenstein" if districtnumber21 == 148
replace districtname21 = "Olpe – Märkischer Kreis I" if districtnumber21 == 149
replace districtname21 = "Märkischer Kreis II" if districtnumber21 == 150
replace districtname21 = "Nordsachsen" if districtnumber21 == 151
replace districtname21 = "Leipzig I" if districtnumber21 == 152
replace districtname21 = "Leipzig II" if districtnumber21 == 153
replace districtname21 = "Leipzig-Land" if districtnumber21 == 154
replace districtname21 = "Meißen" if districtnumber21 == 155
replace districtname21 = "Bautzen I" if districtnumber21 == 156
replace districtname21 = "Görlitz" if districtnumber21 == 157
replace districtname21 = "Sächsische Schweiz-Osterzgebirge" if districtnumber21 == 158
replace districtname21 = "Dresden I" if districtnumber21 == 159
replace districtname21 = "Dresden II – Bautzen II" if districtnumber21 == 160
replace districtname21 = "Mittelsachsen" if districtnumber21 == 161
replace districtname21 = "Chemnitz" if districtnumber21 == 162
replace districtname21 = "Chemnitzer Umland – Erzgebirgskreis II" if districtnumber21 == 163
replace districtname21 = "Erzgebirgskreis I" if districtnumber21 == 164
replace districtname21 = "Zwickau" if districtnumber21 == 165
replace districtname21 = "Vogtlandkreis" if districtnumber21 == 166
replace districtname21 = "Waldeck" if districtnumber21 == 167
replace districtname21 = "Kassel" if districtnumber21 == 168
replace districtname21 = "Werra-Meißner – Hersfeld-Rotenburg" if districtnumber21 == 169
replace districtname21 = "Schwalm-Eder" if districtnumber21 == 170
replace districtname21 = "Marburg" if districtnumber21 == 171
replace districtname21 = "Lahn-Dill" if districtnumber21 == 172
replace districtname21 = "Gießen" if districtnumber21 == 173
replace districtname21 = "Fulda" if districtnumber21 == 174
replace districtname21 = "Main-Kinzig – Wetterau II – Schotten" if districtnumber21 == 175
replace districtname21 = "Hochtaunus" if districtnumber21 == 176
replace districtname21 = "Wetterau I" if districtnumber21 == 177
replace districtname21 = "Rheingau-Taunus – Limburg" if districtnumber21 == 178
replace districtname21 = "Wiesbaden" if districtnumber21 == 179
replace districtname21 = "Hanau" if districtnumber21 == 180
replace districtname21 = "Main-Taunus" if districtnumber21 == 181
replace districtname21 = "Frankfurt am Main I" if districtnumber21 == 182
replace districtname21 = "Frankfurt am Main II" if districtnumber21 == 183
replace districtname21 = "Groß-Gerau" if districtnumber21 == 184
replace districtname21 = "Offenbach" if districtnumber21 == 185
replace districtname21 = "Darmstadt" if districtnumber21 == 186
replace districtname21 = "Odenwald" if districtnumber21 == 187
replace districtname21 = "Bergstraße" if districtnumber21 == 188
replace districtname21 = "Eichsfeld – Nordhausen – Kyffhäuserkreis" if districtnumber21 == 189
replace districtname21 = "Eisenach – Wartburgkreis – Unstrut-Hainich-Kreis" if districtnumber21 == 190
replace districtname21 = "Jena – Sömmerda – Weimarer Land I" if districtnumber21 == 191
replace districtname21 = "Gotha – Ilm-Kreis" if districtnumber21 == 192
replace districtname21 = "Erfurt – Weimar – Weimarer Land II" if districtnumber21 == 193
replace districtname21 = "Gera – Greiz – Altenburger Land" if districtnumber21 == 194
replace districtname21 = "Saalfeld-Rudolstadt – Saale-Holzland-Kreis – Saale-Orla-Kreis" if districtnumber21 == 195
replace districtname21 = "Suhl – Schmalkalden-Meiningen – Hildburghausen – Sonneberg" if districtnumber21 == 196
replace districtname21 = "Neuwied" if districtnumber21 == 197
replace districtname21 = "Ahrweiler" if districtnumber21 == 198
replace districtname21 = "Koblenz" if districtnumber21 == 199
replace districtname21 = "Mosel/Rhein-Hunsrück" if districtnumber21 == 200
replace districtname21 = "Kreuznach" if districtnumber21 == 201
replace districtname21 = "Bitburg" if districtnumber21 == 202
replace districtname21 = "Trier" if districtnumber21 == 203
replace districtname21 = "Montabaur" if districtnumber21 == 204
replace districtname21 = "Mainz" if districtnumber21 == 205
replace districtname21 = "Worms" if districtnumber21 == 206
replace districtname21 = "Ludwigshafen/Frankenthal" if districtnumber21 == 207
replace districtname21 = "Neustadt – Speyer" if districtnumber21 == 208
replace districtname21 = "Kaiserslautern" if districtnumber21 == 209
replace districtname21 = "Pirmasens" if districtnumber21 == 210
replace districtname21 = "Südpfalz" if districtnumber21 == 211
replace districtname21 = "Altötting" if districtnumber21 == 212
replace districtname21 = "Erding – Ebersberg" if districtnumber21 == 213
replace districtname21 = "Freising" if districtnumber21 == 214
replace districtname21 = "Fürstenfeldbruck" if districtnumber21 == 215
replace districtname21 = "Ingolstadt" if districtnumber21 == 216
replace districtname21 = "München-Nord" if districtnumber21 == 217
replace districtname21 = "München-Ost" if districtnumber21 == 218
replace districtname21 = "München-Süd" if districtnumber21 == 219
replace districtname21 = "München-West/Mitte" if districtnumber21 == 220
replace districtname21 = "München-Land" if districtnumber21 == 221
replace districtname21 = "Rosenheim" if districtnumber21 == 222
replace districtname21 = "Bad Tölz-Wolfratshausen – Miesbach" if districtnumber21 == 223
replace districtname21 = "Starnberg – Landsberg am Lech" if districtnumber21 == 224
replace districtname21 = "Traunstein" if districtnumber21 == 225
replace districtname21 = "Weilheim" if districtnumber21 == 226
replace districtname21 = "Deggendorf" if districtnumber21 == 227
replace districtname21 = "Landshut" if districtnumber21 == 228
replace districtname21 = "Passau" if districtnumber21 == 229
replace districtname21 = "Rottal-Inn" if districtnumber21 == 230
replace districtname21 = "Straubing" if districtnumber21 == 231
replace districtname21 = "Amberg" if districtnumber21 == 232
replace districtname21 = "Regensburg" if districtnumber21 == 233
replace districtname21 = "Schwandorf" if districtnumber21 == 234
replace districtname21 = "Weiden" if districtnumber21 == 235
replace districtname21 = "Bamberg" if districtnumber21 == 236
replace districtname21 = "Bayreuth" if districtnumber21 == 237
replace districtname21 = "Coburg" if districtnumber21 == 238
replace districtname21 = "Hof" if districtnumber21 == 239
replace districtname21 = "Kulmbach" if districtnumber21 == 240
replace districtname21 = "Ansbach" if districtnumber21 == 241
replace districtname21 = "Erlangen" if districtnumber21 == 242
replace districtname21 = "Fürth" if districtnumber21 == 243
replace districtname21 = "Nürnberg-Nord" if districtnumber21 == 244
replace districtname21 = "Nürnberg-Süd" if districtnumber21 == 245
replace districtname21 = "Roth" if districtnumber21 == 246
replace districtname21 = "Aschaffenburg" if districtnumber21 == 247
replace districtname21 = "Bad Kissingen" if districtnumber21 == 248
replace districtname21 = "Main-Spessart" if districtnumber21 == 249
replace districtname21 = "Schweinfurt" if districtnumber21 == 250
replace districtname21 = "Würzburg" if districtnumber21 == 251
replace districtname21 = "Augsburg-Stadt" if districtnumber21 == 252
replace districtname21 = "Augsburg-Land" if districtnumber21 == 253
replace districtname21 = "Donau-Ries" if districtnumber21 == 254
replace districtname21 = "Neu-Ulm" if districtnumber21 == 255
replace districtname21 = "Oberallgäu" if districtnumber21 == 256
replace districtname21 = "Ostallgäu" if districtnumber21 == 257
replace districtname21 = "Stuttgart I" if districtnumber21 == 258
replace districtname21 = "Stuttgart II" if districtnumber21 == 259
replace districtname21 = "Böblingen" if districtnumber21 == 260
replace districtname21 = "Esslingen" if districtnumber21 == 261
replace districtname21 = "Nürtingen" if districtnumber21 == 262
replace districtname21 = "Göppingen" if districtnumber21 == 263
replace districtname21 = "Waiblingen" if districtnumber21 == 264
replace districtname21 = "Ludwigsburg" if districtnumber21 == 265
replace districtname21 = "Neckar-Zaber" if districtnumber21 == 266
replace districtname21 = "Heilbronn" if districtnumber21 == 267
replace districtname21 = "Schwäbisch Hall – Hohenlohe" if districtnumber21 == 268
replace districtname21 = "Backnang – Schwäbisch Gmünd" if districtnumber21 == 269
replace districtname21 = "Aalen – Heidenheim" if districtnumber21 == 270
replace districtname21 = "Karlsruhe-Stadt" if districtnumber21 == 271
replace districtname21 = "Karlsruhe-Land" if districtnumber21 == 272
replace districtname21 = "Rastatt" if districtnumber21 == 273
replace districtname21 = "Heidelberg" if districtnumber21 == 274
replace districtname21 = "Mannheim" if districtnumber21 == 275
replace districtname21 = "Odenwald – Tauber" if districtnumber21 == 276
replace districtname21 = "Rhein-Neckar" if districtnumber21 == 277
replace districtname21 = "Bruchsal – Schwetzingen" if districtnumber21 == 278
replace districtname21 = "Pforzheim" if districtnumber21 == 279
replace districtname21 = "Calw" if districtnumber21 == 280
replace districtname21 = "Freiburg" if districtnumber21 == 281
replace districtname21 = "Lörrach – Müllheim" if districtnumber21 == 282
replace districtname21 = "Emmendingen – Lahr" if districtnumber21 == 283
replace districtname21 = "Offenburg" if districtnumber21 == 284
replace districtname21 = "Rottweil – Tuttlingen" if districtnumber21 == 285
replace districtname21 = "Schwarzwald-Baar" if districtnumber21 == 286
replace districtname21 = "Konstanz" if districtnumber21 == 287
replace districtname21 = "Waldshut" if districtnumber21 == 288
replace districtname21 = "Reutlingen" if districtnumber21 == 289
replace districtname21 = "Tübingen" if districtnumber21 == 290
replace districtname21 = "Ulm" if districtnumber21 == 291
replace districtname21 = "Biberach" if districtnumber21 == 292
replace districtname21 = "Bodensee" if districtnumber21 == 293
replace districtname21 = "Ravensburg" if districtnumber21 == 294
replace districtname21 = "Zollernalb – Sigmaringen" if districtnumber21 == 295
replace districtname21 = "Saarbrücken" if districtnumber21 == 296
replace districtname21 = "Saarlouis" if districtnumber21 == 297
replace districtname21 = "St. Wendel" if districtnumber21 == 298
replace districtname21 = "Homburg" if districtnumber21 == 299

* Generate party variable

gen party17 = 1 if cdu17 == 1
replace party17 = 4 if spd17 == 1
replace party17 = 5 if fdp17 == 1
replace party17 = 7 if dielinke17 == 1
replace party17 = 6 if grune17 == 1
replace party17 = 322 if afd17 == 1
replace party17 = 180 if freiewahler17 == 1
replace party17 = 1 if csu17 == 1
replace party17 = 801 if party17 == . 

* Generate vote percentages

rename v17_1_ numberofvotes17

gen valid17 = strpos(var, "Gültige") > 0

gen validvotes17 = numberofvotes17 if valid17 == 1

gsort districtname21 validvotes17
bysort districtname21 : gen numberofvalidvotes17 = validvotes17[1]

drop if strpos(var, "Wahlberechtigte_1_Vor") > 0
drop if strpos(var, "Wähler_1_Vor") > 0
drop if strpos(var, "Ungültige_1_Vor") > 0
drop if strpos(var, "Gültige_1_Vor") > 0

gsort districtname21 -numberofvotes17

bysort districtname21 : gen rank17 = _n

gen numberofvotespercent17 = (numberofvotes17/numberofvalidvotes17)*100

drop if numberofvotespercent17 == .

* Generate rank of parties

gen first17 = party17 if rank17 == 1
gen second17 = party17 if rank17 == 2
gen third17 = party17 if rank17 == 3

bysort districtname21 : gen winner17 = first17[1]
bysort districtname21 : gen runnerup17 = second17[2]
bysort districtname21 : gen thirdplace17 = third17[3]

* Generate margins of victory

bysort districtname21 : gen margin17 = numberofvotespercent17[1] - numberofvotespercent17[2]

* Labelling parties

label define party 1 "CDU/CSU" 4 "SPD" 5 "FDP" 6 "Green" 7 "Die Linke" 180 "FW" 322 "AfD" 88 "Other"
label values winner17 runnerup17 thirdplace17 party

* Selecting, ordering and sorting variables

keep districtnumber21 districtname21 party17 rank17 numberofvotes17 numberofvotespercent17 margin17 winner17 runnerup17 thirdplace17

order districtnumber21 districtname21 party17 rank17 numberofvotes17 numberofvotespercent17 margin17 winner17 runnerup17 thirdplace17

sort districtnumber21 rank17

drop if rank17 > 3

* Drop duplicates

duplicates tag districtnumber21, gen(dup)
duplicates drop districtnumber21, force
drop dup

* Saving file

save DE17_21_results, replace